#!/usr/bin/env python
# -*- coding: utf-8 -*-

#####################################################################################
#Version Histroy
#Ver00 : Initial Version   2007/05/20  RogerZheng
#Ver01 : A.To solve in binary floating point, 1.00 % 0.1 gives 0.09999999999999995
#          instead of the expected Decimal("0.00") returned by decimal floating point
#        B.Fill color at fail test value.
#        2007/05/23      RogerZheng
#Ver02 : A.Cacel ".txt" required  
#        B.Fixed bug for fail test value for MT datalog and TestName for J750
#        C.Fixed re
#        2007/05/29  RogerZheng

#####################################################################################
#   How to Use
#1. Datalog File Name format: TesterName-TestNum-Num.txt
#2. All datalog file need to be same directory
#3. Generator a Excel file: DcCollection.xls
#4. if the file: DcCollection.xls is exist, it will mv it to DcCollection.xls_org


import os
import glob
import shutil
import re
import string
import decimal
import pyExcelerator as PyXLS

UnitRate={"V":"1","MV":"0.001","UV":"0.000001","A":1,"MA":"0.001","UA":"0.000001","NA":"0.000000001","PA":"0.000000000001"}

TitleFont = PyXLS.Font()
TitleFont.height = 20*20
TitleFont.name = 'Verdana'
TitleFont.bold = True
TitleFont.underline = PyXLS.Font.UNDERLINE_DOUBLE
TitleFont.colour_index = 4

TitleStyle = PyXLS.XFStyle()
TitleStyle.font = TitleFont

Font = PyXLS.Font()
Font.name = 'Verdana'
Font.bold = True
Font.height = 8*0x12

Bord = PyXLS.Borders()
Bord.left = 0x01
Bord.right = 0x01
Bord.top = 0x01
Bord.bottom = 0x01

Alg = PyXLS.Alignment()
Alg.horz = PyXLS.Alignment.HORZ_LEFT
Alg.vert = PyXLS.Alignment.VERT_CENTER

#pattern_fore_colour:
#0x00: black; 0x01: white; 0x02: red; 0x03: green; 0x04: blue; 0x05: yellow; 0x06: pink; 0x07: turquoise;

TestNoPat = PyXLS.Pattern()
TestNoPat.pattern = PyXLS.Pattern.SOLID_PATTERN
TestNoPat.pattern_fore_colour = 0x16

LimitPat = PyXLS.Pattern()
LimitPat.pattern = PyXLS.Pattern.SOLID_PATTERN
LimitPat.pattern_fore_colour = 0x05

TestValuePat = PyXLS.Pattern()
TestValuePat.pattern = PyXLS.Pattern.SOLID_PATTERN
TestValuePat.pattern_fore_colour = 0x01

TestFailPat = PyXLS.Pattern()
TestFailPat.pattern = PyXLS.Pattern.SOLID_PATTERN
TestFailPat.pattern_fore_colour = 0x02

TestNoSty = PyXLS.XFStyle()
TestNoSty.font =Font
TestNoSty.alignment = Alg
TestNoSty.pattern = TestNoPat
TestNoSty.borders = Bord

LimitSty = PyXLS.XFStyle()
LimitSty.font = Font
LimitSty.alignment = Alg
LimitSty.pattern = LimitPat
LimitSty.borders = Bord

TestValueSty = PyXLS.XFStyle()
TestValueSty.font = Font
TestValueSty.alignment = Alg
TestValueSty.pattern = TestValuePat
TestValueSty.borders = Bord

TestFailSty = PyXLS.XFStyle()
TestFailSty.font = Font
TestFailSty.alignment = Alg
TestFailSty.pattern = TestFailPat
TestFailSty.borders = Bord

def MTDC2Array(DataFile,TotalSampleCount,TotalTestItemNum,TotalTestNum,TotalSW,TestValue,TestResult,TestUnit,TestLowLmt,TestLowUnit,TestHighLmt,TestHighUnit):
    TempGroups=re.search(r"(\w+)(-)(\d+)(-)(\d+)",os.path.basename(DataFile)).groups()
    TempTesterNo=TempGroups[2].strip()
    SampleCount=TempGroups[4].strip()
    if os.path.isfile(DataFile):
        OpenFileFH=open(DataFile)        
    else:
        print "Error! Not Found File: ",DataFile
    
    SW=""
    #Format for SW:"2007.04.24 14:45  PRONAME=f5f2118400d  ROM NO=      SENSESW=   3 MULTI= 1"
    CompiledSW = re.compile(r"(\s*\d+\.\d+\.\d+\s*\d+:\d+\s*)(PRONAME=\w+\s*)(ROM NO=\w*\s*)(SENSESW=\s*)(\d+\s*)(MULTI=\s*)(\d+)")
    #Format for pass standard parameter:"4   1:  5-  606 mV    0  1  -  100 mV  -  900 mV      5"
    #Format for pass standard parameter:1 757:V 2   680 nA    0  5  - 2.700 uA  -   50 nA    841"
    CompiledStdParam = re.compile(r"^(\d+\s*)(\d*:[\w|\s])(\s*\d*-*)(\s*\d*[.\d]\d*)(\s*\w*)(\s*\d*)(\s*\d*)(\s*[\s*,-])(\s*\d*[.,\d]\d*)(\s*\w*)(\s*[-,\s])(\s*\d*[.,\d]\d*)(\s*\w*)(\s*\d*)$")
    
    #Format for Fail standard parameter:"4   1:  2-3.114  V[7m*[0m   0  1  -  100 mV  -  900 mV      3"
    #Format for Fail standard parameter:"4   1:  2 3.114  V[7m*[0m   0  1    10.0 mV  -  900 mV      3"
    #Format for Fail standard parameter:"4  29:  1 OVER... [7m*[0m   0  3     500 nA  -  500 nA     31"
    CompiledFailParam= re.compile(r"^(\d+\s*)(\d*:[\w|\s])(\s*\d*-*)(\s*\w*[.\w]\w*)(\s*.*\s*)(\x1b\[7m\*\x1b\[0m)(\s*\d*)(\s*\d*)(\s*[\s*,-])(\s*\d*[.,\d]\d*)(\s*\w*)(\s*[-,\s])(\s*\d*[.,\d]\d*)(\s*\w*)(\s*\d*)$")
    
    SampleNo=0
    TempSite={}
    Lines=OpenFileFH.readlines()
    
    for Line in Lines:
        #Compare Pass Parameter
        if CompiledSW.search(Line):
            SW=CompiledSW.search(Line).group(5).strip()
            if TotalSW < eval(SW):
                TotalSW= eval(SW)
            
        if SW:
            #Line="4  29:  1 OVER... [7m*[0m   0  3     500 nA  -  500 nA     31"
            if CompiledStdParam.search(Line):
                Temp=CompiledStdParam.search(Line).groups()
                SiteNum=Temp[0].strip()
                if TempSite.has_key(SiteNum):
                    SampleNo=TempSite[SiteNum]
                else:
                    SampleNo=SampleCount
                    TempSite[SiteNum]=SampleCount
                    SampleCount=eval(SampleCount)+1
                    
                TestNum=Temp[1].strip()
                TestItemNum=Temp[2].strip()
                TempValue=Temp[3].strip()
                TempUnit=Temp[4].strip()
                
                #Special for format: TestNum:\w or TestNum:
                if re.search(r":\w",Temp[1]):
                    TestNum=Temp[1].strip()[:-2]
                elif re.search(r":",Temp[1]):
                    TestNum=Temp[1].strip()[:-1]
                #Special for format:TestItemNum-TestValue    
                if re.search(r"-",Temp[2]):
                    TestItemNum=Temp[2][:-1].strip()
                    TempValue="".join(("-",Temp[3].strip()))
                # Convert Value unit to Limit unit
                if string.upper(Temp[4].strip()) <> string.upper(Temp[9].strip()):
                    if UnitRate.has_key(string.upper(Temp[4].strip())) and UnitRate.has_key(string.upper(Temp[9].strip())):
                        #TempValue=eval(TempValue)*UnitRate[string.upper(Temp[4].strip())]/UnitRate[string.upper(Temp[9].strip())]
                        TempValue=decimal.Decimal(TempValue)*decimal.Decimal(UnitRate[string.upper(Temp[4].strip())])/decimal.Decimal(UnitRate[string.upper(Temp[9].strip())])
                        TempUnit=Temp[9].strip()
                
                TempKey="".join(("Sample",str(SampleNo),"Tester",TempTesterNo,"SW",SW,"TestNum",TestNum,"TestItemNum",TestItemNum))
                TestValue[TempKey]=str(TempValue)
                #TesterNo[TempKey]=TempTesterNo
                TestResult[TempKey]="PASS"
                TestUnit[TempKey]=TempUnit
                TestHighLmt[TempKey]=Temp[8].strip()
                TestHighUnit[TempKey]=Temp[9].strip()
                TestLowLmt[TempKey]=Temp[11].strip()
                TestLowUnit[TempKey]=Temp[12].strip()
                
                if re.search(r"-",Temp[7]):
                    TestHighLmt[TempKey]="".join(("-",Temp[8].strip()))
                if re.search(r"-",Temp[10]):
                    TestLowLmt[TempKey]="".join(("-",Temp[11].strip()))
                
                if TotalTestNum < eval(TestNum):
                    TotalTestNum = eval(TestNum)
                if TotalTestItemNum < eval(TestItemNum):
                    TotalTestItemNum = eval(TestItemNum)
                if TotalSampleCount < SampleCount:
                    TotalSampleCount=SampleCount
                    
            #Compare Fail Parameter
            elif CompiledFailParam.search(Line):
                Temp=CompiledFailParam.search(Line).groups()
                SiteNum=Temp[0].strip()
                if TempSite.has_key(SiteNum):
                    SampleNo=TempSite[SiteNum]
                else:                    
                    TempSite[SiteNum]=SampleCount
                    SampleNo=SampleCount
                    SampleCount=eval(SampleCount)+1
                TestNum=Temp[1].strip()
                TestItemNum=Temp[2].strip()
                TempValue=Temp[3].strip()
                TempUnit=Temp[4].strip()
                #Special for format: TestNum:\w or TestNum:
                if re.search(r":\w",Temp[1]):
                    TestNum=Temp[1].strip()[:-2]
                elif re.search(r":",Temp[1]):
                    TestNum=Temp[1].strip()[:-1]
                #Special for format:TestItemNum-TestValue 
                if re.search(r"-",Temp[2]):
                    TestItemNum=Temp[2][:-1].strip()
                    TempValue="".join(("-",Temp[3].strip()))
                # Convert Value unit to Limit unit
                if string.upper(Temp[4].strip()) <> string.upper(Temp[9].strip()):
                    if UnitRate.has_key(string.upper(Temp[4].strip())) and UnitRate.has_key(string.upper(Temp[9].strip())):
                        #TempValue=eval(TempValue)*UnitRate[string.upper(Temp[4].strip())]/UnitRate[string.upper(Temp[9].strip())]
                        TempValue=decimal.Decimal(TempValue)*decimal.Decimal(UnitRate[string.upper(Temp[4].strip())])/decimal.Decimal(UnitRate[string.upper(Temp[9].strip())])
                        TempUnit=Temp[9].strip()
                
                TempKey="".join(("Sample",str(SampleNo),"Tester",TempTesterNo,"SW",SW,"TestNum",TestNum,"TestItemNum",TestItemNum))
                TestValue[TempKey]=TempValue
                #TesterNo[TempKey]=TempTesterNo
                TestResult[TempKey]="FAIL"
                TestUnit[TempKey]=TempUnit
                TestHighLmt[TempKey]=Temp[9].strip()
                TestHighUnit[TempKey]=Temp[10].strip()
                TestLowLmt[TempKey]=Temp[12].strip()
                TestLowUnit[TempKey]=Temp[13].strip()
                
                if re.search(r"-",Temp[8]):
                    TestHighLmt[TempKey]="".join(("-",Temp[9].strip()))
                if re.search(r"-",Temp[11]):
                    TestLowLmt[TempKey]="".join(("-",Temp[12].strip()))
                    
                if TotalTestNum < eval(TestNum):
                    TotalTestNum = eval(TestNum)
                #print Line
                #print "TestItemNum:",TestItemNum    
                if TotalTestItemNum < eval(TestItemNum):
                    TotalTestItemNum = eval(TestItemNum)
                
                if TotalSampleCount < SampleCount:
                    TotalSampleCount=SampleCount
        else:
            pass
            #print "Error! Datalog File is not complete in File: ", DataFile
                    
    return(TotalSampleCount,TotalTestItemNum,TotalTestNum,TotalSW,TestValue,TestResult,TestUnit,TestLowLmt,TestLowUnit,TestHighLmt,TestHighUnit)            
                
def MTFUNC2Array(DataFile):
    
    if os.path.isfile(DataFile):
        OpenFileFH=open(DataFile)
    else:
        print "Error! Not Found File: ",DataFile
    
    TestValue={}
        
    TotalTestNum=0
    TotalTestSiteNum=0
    SW=""
    
    
    #Format for Pass Standard Functional:"2 756:F  0< 0>2562    0  5 NGSTOP%0000 PC%0a02    838 RUNBIT=6662"
    CompiledStdFunc = re.compile(r"(\d+\s*)(\d+:\w\s*)(\d+<\s*\d+>\s*\d+\s*)(\d+\s*)(\d+\s*)(NGSTOP%\w+\s+)(PC%\w+\s*)(\d+\s*)(RUNBIT=\d+)")
    
    #Format for Pass Standard Functional:"4 890:F  3< 0>1364[7m*[0m   0  0 NGSTOP%0554 PC%0556    153 RUNBIT=922816"
    CompiledFailFunc = re.compile(r"(\d+\s*)(\d+:\w\s*)(\d+<\s*\d+>\s*\d+\s*)(\x1b\[7m\*\x1b\[0m\s*)(\d+\s*)(\d+\s*)(NGSTOP%\w*\s*)(PC%\w+\s*)(\d+\s*)(RUNBIT=\w+)")
    
    Lines=OpenFileFH.readlines()
    
    for Line in Lines:
            
        #Compare Pass Function
        if CompiledStdFunc.search(Line):
            Temp=CompiledStdFunc.search(Line).groups()
            TempKey="B"
            SiteNum=Temp[0]
            TestNum=Temp[1].strip()[:-2]
            TestValue[TempKey]="PASS"
            
            if TotalTestNum < eval(TestNum):
                TotalTestNum = eval(TestNum)
                
            if TotalTestSiteNum < eval(SiteNum):
                TotalTestSiteNum = eval(SiteNum)
        elif CompiledFailFunc.search(Line):
            Temp=CompiledFailFunc.search(Line).groups()
            TempKey="C"
            SiteNum=Temp[0]
            TestNum=Temp[1].strip()[:-2]
            TestValue[TempKey]="FAIL"    
            
            if TotalTestNum < eval(TestNum):
                TotalTestNum = eval(TestNum)
                
            if TotalTestSiteNum < eval(SiteNum):
                TotalTestSiteNum = eval(SiteNum)
    return(TotalTestSiteNum,TotalTestNum,TestValue)        

def J750DC2Array(DataFile,TotalSampleCount,TotalTestItemNum,TotalTestNum,TotalSW,TestValue,TestResult,TestUnit,TestLowLmt,TestLowUnit,TestHighLmt,TestHighUnit):
    TempGroups=re.search(r"(\w+)(-)(\d+)(-)(\d+)",os.path.basename(DataFile)).groups()
    TempTesterNo=TempGroups[2].strip()
    SampleCount=TempGroups[4].strip()
    if os.path.isfile(DataFile):
        OpenFileFH=open(DataFile)
    else:
        print "Error! Not Found File: ",DataFile
    
    SW=""
    #Format for SW:"2007.04.24 14:45  PRONAME=f5f2118400d  ROM NO=      SENSESW=   3 MULTI= 1"
    CompiledSW = re.compile(r"(\s+Job Name:\s+)(\w+\s+)")
    
    #Format for pass standard parameter:" 1       0    PASS     TEST1                     P1    0       -900.0000 mV   -631.3431 mV   -100.0000 mV   -500.0000 uA   0       "
    CompiledStdParam = re.compile(r"^(\s*\d+\s+)(\d+\s+)(\w+\s+)(\w+\s+)(\w+\s+)(\d+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(\d+\s+)$")
    #Format for Special TestNum:" 1       0    PASS     TEST1.0                   P1    0       -900.0000 mV   -628.3959 mV   -100.0000 mV   -500.0000 uA   0       "
    CompiledSpeTNParam=re.compile(r"^(\s*\d+\s+)(\d+\s+)(\w+\s+)(\w+\.\d+\s+)(\w+\s+)(\d+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(\d+\s+)$")
    #Format for Special Parameter:" 753     0    PASS     TEST753                   GANGPINS-1      300.0000 nA    661.2502 nA    2.7000 uA      5.5000 V       0       "
    CompiledSpeParam = re.compile(r"^(\s*\d+\s+)(\d+\s+)(\w+\s+)(\w+\s+)(\w+.\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(.\d*.\d*\s+)(\w+\s+)(\d+\s+)$")
    
    SampleNo =0
    TempSite={}
    Lines=OpenFileFH.readlines()
    
    for Line in Lines:
        #Compare Pass Parameter
        if CompiledSW.search(Line):
            SW=CompiledSW.search(Line).group(2).strip()[2:]
            if TotalSW < eval(SW):
                TotalSW= eval(SW)
            
        if SW:
            #Standard Parmameter
            if CompiledStdParam.search(Line):
                Temp=CompiledStdParam.search(Line).groups() 
                SiteNum=Temp[1].strip()
                if TempSite.has_key(SiteNum):
                    SampleNo=TempSite[SiteNum]
                else:
                    TempSite[SiteNum]=SampleCount                
                    SampleNo=SampleCount                    
                    SampleCount=eval(SampleCount)+1  
                    
                TestNum=Temp[3].strip()[4:]
                TestItemNum=re.search(r"(\d+)",Temp[4].strip()).group(1)
                TempValue=Temp[8].strip()
                TempUnit=Temp[9].strip()
                
                # Convert Value unit to Limit unit
                if string.upper(Temp[9].strip()) <> string.upper(Temp[11].strip()):
                    if UnitRate.has_key(string.upper(Temp[9].strip())) and UnitRate.has_key(string.upper(Temp[11].strip())):
                        #TempValue=eval(TempValue)*UnitRate[string.upper(Temp[9].strip())]/UnitRate[string.upper(Temp[11].strip())]
                        TempValue=decimal.Decimal(TempValue)*decimal.Decimal(UnitRate[string.upper(Temp[9].strip())])/decimal.Decimal(UnitRate[string.upper(Temp[11].strip())])
                        TempUnit=Temp[11].strip()
                                
                TempKey="".join(("Sample",str(SampleNo),"Tester",TempTesterNo,"SW",SW,"TestNum",TestNum,"TestItemNum",TestItemNum))
                TestValue[TempKey]=str(TempValue)
                #TesterNo[TempKey]=TempTesterNo
                TestResult[TempKey]=Temp[2].strip()
                TestUnit[TempKey]=TempUnit
                TestHighLmt[TempKey]=Temp[10].strip()
                TestHighUnit[TempKey]=Temp[11].strip()
                TestLowLmt[TempKey]=Temp[6].strip()
                TestLowUnit[TempKey]=Temp[7].strip()
                 
                if TotalTestNum < eval(TestNum):
                    TotalTestNum = eval(TestNum)
                if TotalTestItemNum < eval(TestItemNum):
                    TotalTestItemNum = eval(TestItemNum)
                if TotalSampleCount < SampleCount:
                    TotalSampleCount=SampleCount
                    
            #Special Test Name Parmameter
            if CompiledSpeTNParam.search(Line):
                Temp=CompiledSpeTNParam.search(Line).groups() 
                SiteNum=Temp[1].strip()
                if TempSite.has_key(SiteNum):
                    SampleNo=TempSite[SiteNum]
                else:
                    TempSite[SiteNum]=SampleCount
                    SampleNo=SampleCount
                    SampleCount=eval(SampleCount)+1
                    
                TestNum=re.search(r"(\w+)(\.)(\d+)",Temp[3]).group(1).strip()[4:]
                TestItemNum=re.search(r"(\d+)",Temp[4].strip()).group(1)
                TempValue=Temp[8].strip()
                TempUnit=Temp[9].strip()
                
                # Convert Value unit to Limit unit
                if string.upper(Temp[9].strip()) <> string.upper(Temp[11].strip()):
                    if UnitRate.has_key(string.upper(Temp[9].strip())) and UnitRate.has_key(string.upper(Temp[11].strip())):
                        #TempValue=eval(TempValue)*UnitRate[string.upper(Temp[9].strip())]/UnitRate[string.upper(Temp[11].strip())]
                        TempValue=decimal.Decimal(TempValue)*decimal.Decimal(UnitRate[string.upper(Temp[9].strip())])/decimal.Decimal(UnitRate[string.upper(Temp[11].strip())])
                        TempUnit=Temp[11].strip()
                                
                TempKey="".join(("Sample",str(SampleNo),"Tester",TempTesterNo,"SW",SW,"TestNum",TestNum,"TestItemNum",TestItemNum))
                TestValue[TempKey]=str(TempValue)
                #TesterNo[TempKey]=TempTesterNo
                TestResult[TempKey]=Temp[2].strip()
                TestUnit[TempKey]=TempUnit
                TestHighLmt[TempKey]=Temp[10].strip()
                TestHighUnit[TempKey]=Temp[11].strip()
                TestLowLmt[TempKey]=Temp[6].strip()
                TestLowUnit[TempKey]=Temp[7].strip()
                 
                if TotalTestNum < eval(TestNum):
                    TotalTestNum = eval(TestNum)
                if TotalTestItemNum < eval(TestItemNum):
                    TotalTestItemNum = eval(TestItemNum)
                if TotalSampleCount < SampleCount:
                    TotalSampleCount=SampleCount
                
            # Special Parmater            
            elif CompiledSpeParam.search(Line):
                Temp=CompiledSpeParam.search(Line).groups()
                SiteNum=Temp[1].strip()
                if TempSite.has_key(SiteNum):
                    SampleNo=TempSite[SiteNum]
                else :
                    TempSite[SiteNum]=SampleCount
                    SampleNo=SampleCount
                    SampleCount=eval(SampleCount)+1
                    
                TestNum=Temp[3].strip()[4:]
                TestItemNum=Temp[4].strip()[-1]
                TempValue=Temp[7].strip()
                TempUnit=Temp[8].strip()
                
                # Convert Value unit to Limit unit
                if string.upper(Temp[8].strip()) <> string.upper(Temp[10].strip()):
                    if UnitRate.has_key(string.upper(Temp[8].strip())) and UnitRate.has_key(string.upper(Temp[10].strip())):
                        #TempValue=eval(TempValue)*UnitRate[string.upper(Temp[8].strip())]/UnitRate[string.upper(Temp[10].strip())]
                        TempValue=decimal.Decimal(TempValue)*decimal.Decimal(UnitRate[string.upper(Temp[8].strip())])/decimal.Decimal(UnitRate[string.upper(Temp[10].strip())])
                        TempUnit=Temp[10].strip()
                
                TempKey="".join(("Sample",str(SampleNo),"Tester",TempTesterNo,"SW",SW,"TestNum",TestNum,"TestItemNum",TestItemNum))
                TestValue[TempKey]=str(TempValue)
                #TesterNo[TempKey]=TempTesterNo
                TestResult[TempKey]=Temp[2].strip()
                TestUnit[TempKey]=TempUnit
                TestHighLmt[TempKey]=Temp[9].strip()
                TestHighUnit[TempKey]=Temp[10].strip()
                TestLowLmt[TempKey]=Temp[5].strip()
                TestLowUnit[TempKey]=Temp[6].strip()
                 
                if TotalTestNum < eval(TestNum):
                    TotalTestNum = eval(TestNum)
                if TotalTestItemNum < eval(TestItemNum):
                    TotalTestItemNum = eval(TestItemNum)
                if TotalSampleCount < SampleCount:
                    TotalSampleCount=SampleCount
            
        else:
            pass
            #print "Error! Datalog File is not complete in File: ", DataFile
                    
    return(TotalSampleCount,TotalTestItemNum,TotalTestNum,TotalSW,TestValue,TestResult,TestUnit,TestLowLmt,TestLowUnit,TestHighLmt,TestHighUnit)            
            
def Raw2Excel(TotalSampleCount,JTesterNum,MTesterNum,TotalSW,TotalTestNum,TotalTestItemNum,MTestValue,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit,JTestValue,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit):
    
    TesterNum=JTesterNum
    if JTesterNum < MTesterNum:
        TesterNum = MTesterNum
    WorkBkStCount=0
    SameSite=False
    for Index1 in range(1,TotalSampleCount+1):
        SameSite=True
        for Index2 in TotalSW: 
            JStartColum = InitCellColum + MTesterNum +4 
            MStartColum = InitCellColum
            MStartRow={}
            JStartRow={}
            for Temp1 in range(1,TotalSampleCount+1):
                for Temp2 in TotalSW:
                    for Temp3 in range(1,TesterSize+1):
                        RowTempKey="".join(("S",str(Temp1),Temp2,"Tester",str(Temp3)))
                        MStartRow[RowTempKey] = InitCellRow
                        JStartRow[RowTempKey] = InitCellRow
            for Index3 in range(TotalTestNum+1):
                for Index4 in range(TotalTestItemNum+1):
                    for Index5  in range(TesterNum+1):
                        SampleId=""
                        TempKey="".join(("Sample",str(Index1),"Tester",str(Index5),Index2,"TestNum",str(Index3),"TestItemNum",str(Index4)))
                        if MTestValue.has_key(TempKey) or JTestValue.has_key(TempKey):
                            SampleId="".join(("S",str(Index1),Index2))
                            RowKey="".join(("S",str(Index1),Index2,"Tester",str(Index5)))
                            if SameSite:
                                SameSite = False
                            if SampleId in WritedSample:
                                pass
                            else:
                                WorkSt = WorkBk.add_sheet("".join(("S",str(Index1),"-",Index2)))
                                WorkSt.write(1,1,"".join(("Sample",str(Index1),"-",Index2)),TitleStyle)
                                WorkSt.write(JStartRow[RowKey],MStartColum,"Test No.",TestNoSty)
                                WorkSt.write(JStartRow[RowKey],MStartColum+1,"Low Lmt",LimitSty)
                                Temp1=1
                                for Temp1 in range(1,MTesterNum+1):
                                    WorkSt.write(JStartRow[RowKey],MStartColum+1+Temp1,"".join(("MT-",str(Temp1))),TestNoSty)
                                WorkSt.write(JStartRow[RowKey],MStartColum+2+Temp1,"High Lmt",LimitSty) 
                                WorkSt.write(JStartRow[RowKey],MStartColum+3+Temp1,"High Unit",LimitSty)
                                
                                WorkSt.write(JStartRow[RowKey],JStartColum,"Test No.",TestNoSty)
                                WorkSt.write(JStartRow[RowKey],JStartColum+1,"Low Lmt",LimitSty)
                                Temp2=1
                                for Temp2 in range(1,JTesterNum+1):
                                    WorkSt.write(JStartRow[RowKey],JStartColum+1+Temp2,"".join(("J750-",str(Temp2))),TestNoSty)
                                WorkSt.write(JStartRow[RowKey],JStartColum+2+Temp2,"High Lmt",LimitSty) 
                                WorkSt.write(JStartRow[RowKey],JStartColum+3+Temp2,"High Unit",LimitSty)  
                                for Temp1 in range(1,TotalSampleCount+1):
                                    for Temp2 in TotalSW:
                                        for Temp3 in range(1,TesterNum+1):
                                            RowTempKey="".join(("S",str(Temp1),Temp2,"Tester",str(Temp3)))
                                            MStartRow[RowTempKey] =  MStartRow[RowTempKey]+1
                                            JStartRow[RowTempKey] = JStartRow[RowTempKey] +1
                                WritedSample.append(SampleId)
                                WorkBkStCount=WorkBkStCount+1
                            if MTestValue.has_key(TempKey):
                                WorkSt.write(MStartRow[RowKey],MStartColum,"".join((str(Index3),":",str(Index4))),TestNoSty)
                                WorkSt.write(MStartRow[RowKey],MStartColum+1,MTestLowLmt[TempKey],LimitSty)
                                if MTestResult[TempKey] =="PASS":
                                    WorkSt.write(MStartRow[RowKey],MStartColum+1+Index5,MTestValue[TempKey],TestValueSty)
                                elif MTestResult[TempKey] =="FAIL":
                                    WorkSt.write(MStartRow[RowKey],MStartColum+1+Index5,MTestValue[TempKey],TestFailSty)
                                WorkSt.write(MStartRow[RowKey],MStartColum+2+MTesterNum,MTestHighLmt[TempKey],LimitSty)
                                WorkSt.write(MStartRow[RowKey],MStartColum+3+MTesterNum,MTestHighUnit[TempKey],LimitSty)
                                MStartRow[RowKey] = MStartRow[RowKey] + 1 
                            if JTestValue.has_key(TempKey):
                                WorkSt.write(JStartRow[RowKey],JStartColum,"".join((str(Index3),":",str(Index4))),TestNoSty)
                                WorkSt.write(JStartRow[RowKey],JStartColum+1,JTestLowLmt[TempKey],LimitSty)
                                if JTestResult[TempKey] =="PASS":
                                    WorkSt.write(JStartRow[RowKey],JStartColum+1+Index5,JTestValue[TempKey],TestValueSty)
                                elif JTestResult[TempKey] =="FAIL":
                                    WorkSt.write(JStartRow[RowKey],JStartColum+1+Index5,JTestValue[TempKey],TestFailSty)
                                WorkSt.write(JStartRow[RowKey],JStartColum+2+JTesterNum,JTestHighLmt[TempKey],LimitSty)
                                WorkSt.write(JStartRow[RowKey],JStartColum+3+JTesterNum,JTestHighUnit[TempKey],LimitSty)
                                JStartRow[RowKey] = JStartRow[RowKey] + 1 
    (JSampleCount,JTotalTestItemNum,JTotalTestNum,JTotalSW,JTestValue,JTesterNo,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit)=(0,0,0,0,{},{},{},{},{},{},{},{})
    (MSampleCount,MTotalTestItemNum,MTotalTestNum,MTotalSW,MTestValue,MTesterNo,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit)=(0,0,0,0,{},{},{},{},{},{},{},{})
    TotalSampleCount = 0
    TotalSW = 0
    TotalTestNum = 0
    TotalTestItemNum = 0
    JTestValue={}
    MTestValue={}
    return WorkBkStCount
if __name__ == "__main__":
    
    CurrentPath = os.getcwd()
    WorkBkName= os.path.join(CurrentPath,"DcCollection.xls")
    WarningFile=os.path.join(CurrentPath,"Warning.txt")
    
    if os.path.isfile(WarningFile):
        os.unlink(WarningFile)
        
    SampleSize=8
    TesterSize=16
    InitCellRow=4
    InitCellColum=3
    
    if os.path.isfile(WorkBkName):
        try:
            shutil.move(WorkBkName,"".join((WorkBkName,"_org")))
        except WindowsError:
            print "Please Close the file: \n", WorkBkName," ! \n And Re-run the program!"
    WritedSample=[]
    WorkBk=PyXLS.Workbook()
    Files=sorted(glob.glob("*"))
        
    (JSampleCount,JTotalTestItemNum,JTotalTestNum,JTotalSW,JTestValue,JTesterNo,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit)=(0,0,0,0,{},{},{},{},{},{},{},{})
    (MSampleCount,MTotalTestItemNum,MTotalTestNum,MTotalSW,MTestValue,MTesterNo,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit)=(0,0,0,0,{},{},{},{},{},{},{},{})
    TotalSampleCount = 0
    #TotalSW = ["SW2","SW3","SW9","SW8"]
    TotalSW = ["SW2"]
    TotalTestNum = 0
    TotalTestItemNum = 0
    JTempTester=0
    MTempTester=0
    WorkBkStCount=0
    
    for Index in range(TesterSize+1):
        JTotalSampleCount=0
        MTotalSampleCount=0
        for File in Files:        
                
            if re.search(r"(^J750-)(\d+)(-)(\d+)",File):
                if re.search(r"(J750-)(\d+)(-)(\d+)",File).group(2).strip()==str(Index+1):
                    (JTotalSampleCount,JTotalTestItemNum,JTotalTestNum,JTotalSW,JTestValue,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit) =J750DC2Array(File,JTotalSampleCount,JTotalTestItemNum,JTotalTestNum,JTotalSW,JTestValue,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit)
                    if JTempTester < eval(re.search(r"(J750-)(\d+)(-)(\d+)",File).group(2).strip()):
                        JTempTester = eval(re.search(r"(J750-)(\d+)(-)(\d+)",File).group(2).strip())
            if re.search(r"(^MT-)(\d+)(-)(\d+)",File):
                if re.search(r"(MT-)(\d+)(-)(\d+)",File).group(2).strip() == str(Index+1):
                    (MTotalSampleCount,MTotalTestItemNum,MTotalTestNum,MTotalSW,MTestValue,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit) = MTDC2Array(File,MTotalSampleCount,MTotalTestItemNum,MTotalTestNum,MTotalSW,MTestValue,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit)
                    if MTempTester < eval(re.search(r"(MT-)(\d+)(-)(\d+)",File).group(2).strip()):
                        MTempTester = eval(re.search(r"(MT-)(\d+)(-)(\d+)",File).group(2).strip())
                    
        if TotalSampleCount < MTotalSampleCount:
            TotalSampleCount = MTotalSampleCount
        elif JTotalSampleCount:
            TotalSampleCount = JTotalSampleCount
            
        if TotalSW < MTotalSW:
            TotalSW = MTotalSW
        elif TotalSW <JTotalSW:
            TotalSW=JTotalSW
        if TotalTestNum < MTotalTestNum:
            TotalTestNum = MTotalTestNum
        elif TotalTestNum < JTotalTestNum:
            TotalTestNum = JTotalTestNum
            
        if TotalTestItemNum < MTotalTestItemNum:
            TotalTestItemNum = MTotalTestItemNum
        elif TotalTestItemNum < JTotalTestItemNum:
            TotalTestItemNum =JTotalTestItemNum
    
    if len(JTestValue)>0 or len(MTestValue) >0:
        WorkBkStCount=Raw2Excel(TotalSampleCount,JTempTester,MTempTester,TotalSW,TotalTestNum,TotalTestItemNum,MTestValue,MTestResult,MTestUnit,MTestLowLmt,MTestLowUnit,MTestHighLmt,MTestHighUnit,JTestValue,JTestResult,JTestUnit,JTestLowLmt,JTestLowUnit,JTestHighLmt,JTestHighUnit)
    if WorkBkStCount>0:
        WorkBk.save(WorkBkName) 
    else:
        WarningFileFH=open(WarningFile,"w")
        WarningFileFH.write("No data you want to collection was foun!")